import pandas as pd
import os


def hb_gljy():
    all_file_name = set()
    all_data_li = []
    # 存放excel的路径
    file_name_li = os.listdir(r"C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联交易\合并关联交易_数据")
    # 读取所有excel的名字
    for file_name in file_name_li:
        file_path_li = os.path.join(r"C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联交易\合并关联交易_数据", file_name)
        all_data = pd.read_excel(file_path_li, sheet_name=None, header=None, skiprows=4)
        col = pd.read_excel(file_path_li, sheet_name='关联交易', skiprows=3, index_col=0)
        all_data_li.append(all_data)
        # 读取所有excel数据
        for name in all_data:
            all_file_name.add(name)
    # 定义存放结果的路径
    writer = pd.ExcelWriter("C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联交易\合并关联交易_结果\合并关联交易结果.xlsx")

    sheet_name = "关联交易"
    data_li = []
    for data in all_data_li:
        # print(data[sheet_name])

        n_rows = data_li.append(data[sheet_name])
    # 拼接sheet页内容
    group_data = pd.concat(data_li)
    group_data = pd.DataFrame(data=group_data)
    # 删除空行
    group_data.dropna(how='all', inplace=True)
    #group_data.drop(group_data.columns[12:], axis=1, inplace=True)
    #group_data.columns = list(col.keys())[:12]
    # 设置结果excel的列名
    try:
        group_data.columns = list(col.keys())
    except:
        print(group_data.columns)
        print(list(col.keys()))
    group_data.to_excel(writer, sheet_name=sheet_name, index=False)
    writer.save()

def hb_glwl():
    all_file_name = set()
    all_data_li = []

    file_name_li = os.listdir(r"C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_数据")
    for file_name in file_name_li:
        file_path_li = os.path.join(r"C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_数据", file_name)
        # print(file_path_li)
        all_data = pd.read_excel(file_path_li, sheet_name=None, header=None, skiprows=4)
        col = pd.read_excel(file_path_li, sheet_name='关联往来', skiprows=3)
        all_data_li.append(all_data)
        for name in all_data:
            all_file_name.add(name)
    # print(all_data_li)
    # print(all_file_name)
    # print(list(col.keys())[:12])
    writer = pd.ExcelWriter("C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_结果\关联往来合并结果.xlsx")

    sheet_name = "关联往来"
    data_li = []
    for data in all_data_li:
        # print(data[sheet_name])
        n_rows = data_li.append(data[sheet_name])
    group_data = pd.concat(data_li)
    group_data = pd.DataFrame(data=group_data)
    group_data.dropna(how='all', inplace=True)
    # group_data = group_data.reset_index()
    # group_data['index'] = range(len(group_data))
    # group_data.rename(columns={'index':'id'}, inplace=True)
    # print(group_data)
    group_data.drop(group_data.columns[14:], axis=1, inplace=True)
    # print(group_data)
    #group_data.columns = list(col.keys())[:12]
    group_data.columns = list(col.keys())[:14]
    group_data.to_excel(writer, sheet_name=sheet_name, index=False)
    writer.save()


def hb_zcfzb():
    all_file_name = set()
    all_data_li = []
    file_name_li = os.listdir(r"C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并资产负债表\合并资产负债表_数据")

    for file_name in file_name_li:
        file_path_li = os.path.join(r"C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并资产负债表\合并资产负债表_数据", file_name)
        # print(file_path_li)
        all_data = pd.read_excel(file_path_li, sheet_name=None)
        # print(all_data["资产负债表"])
        all_data_li.append(all_data)
        for name in all_data:
            all_file_name.add(name)
    # print(all_data_li)

    # writer = pd.ExcelWriter("C:\合并报表\结果\资产负债合并结果.xlsx")
    writer_sum = pd.ExcelWriter("C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并资产负债表\合并资产负债表_结果\资产负债加和结果.xlsx")
    writer_jzrzmje = pd.ExcelWriter("C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并资产负债表\合并资产负债表_结果\资产负债基准日账面金额.xlsx")
    writer_sjcy = pd.ExcelWriter("C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并资产负债表\合并资产负债表_结果\资产负债审计差异金额.xlsx")
    writer_sjrdje = pd.ExcelWriter("C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并资产负债表\合并资产负债表_结果\资产负债审计认定金额.xlsx")
    sheet_name = "资产负债表"
    data_li = []
    company_names = []
    sum = 0
    count = 1
    jzrzmje_lst = []
    sjcy_lst = []
    sjrdje_lst = []
    for data in all_data_li:
        print(data[sheet_name])
        company_names.append(data[sheet_name].iloc[1].values.tolist()[0][5:])
        jzrzmje_lst.append(data[sheet_name]['Unnamed: ' + str(count)].iloc[5:])
        sjcy_lst.append(data[sheet_name]['Unnamed: ' + str(count + 1)].iloc[5:])
        sjrdje_lst.append(data[sheet_name]['Unnamed: ' + str(count + 2)].iloc[5:])
        # 加和行数据
        sum += data[sheet_name][['Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5']].iloc[5:68]
        n_rows = data_li.append(data[sheet_name].drop([0, 1, 2, 3, 69, 70]))
    # 插入列名为‘拦次’的数据
    sum.insert(loc=0, column='拦次', value=data[sheet_name]['资产负债表'].iloc[5:])
    # sum = pd.DataFrame()
    # 修改列名
    sum.rename(columns={'Unnamed: 1':'基准日账面金额', 'Unnamed: 2':'审计差异', 'Unnamed: 3':'审计认定金额'}, inplace=True)
    sum.drop('Unnamed: 4', axis=1, inplace=True)
    sum.drop('Unnamed: 5', axis=1, inplace=True)
    # print(sum.dtype)
    sum.to_excel(writer_sum, sheet_name=sheet_name, index=False)
    writer_sum.save()

    jzrzmje_data = pd.DataFrame()
    # 循环写入结果数据
    for i in range(len(jzrzmje_lst)):
        jzrzmje_data.insert(loc=0, column=company_names[i], value=jzrzmje_lst[i])
    jzrzmje_data.insert(loc=0, column='拦次', value=data[sheet_name]['资产负债表'].iloc[5:68])
    jzrzmje_data.to_excel(writer_jzrzmje, sheet_name=sheet_name)
    writer_jzrzmje.save()

    sjcy_data = pd.DataFrame()
    for i in range(len(sjcy_lst)):
        sjcy_data.insert(loc=0, column=company_names[i], value=sjcy_lst[i])
    sjcy_data.insert(loc=0, column='拦次', value=data[sheet_name]['资产负债表'].iloc[5:68])
    sjcy_data.to_excel(writer_sjcy, sheet_name=sheet_name)
    writer_sjcy.save()

    sjrdje_data = pd.DataFrame()
    for i in range(len(sjcy_lst)):
        sjrdje_data.insert(loc=0, column=company_names[i], value=sjrdje_lst[i])
    sjrdje_data.insert(loc=0, column='拦次', value=data[sheet_name]['资产负债表'].iloc[5:68])
    sjrdje_data.to_excel(writer_sjrdje, sheet_name=sheet_name)
    writer_sjrdje.save()


# 转换excel->csv
def xlsxToCsv():
    data_xlsx = pd.read_excel('C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_结果\关联往来合并结果.xlsx', index_col=0)
    data_xlsx.to_csv('C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_结果\关联往来合并结果.csv', encoding='utf-8')


def db_glwl():
    hb_glwl()
    xlsxToCsv()
    df = pd.read_csv('C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_结果\关联往来合并结果.csv', engine='python')
    df = df.reset_index()
    df['index'] = range(len(df))
    df.rename(columns={'index': 'id'}, inplace=True)
    print(df)
    # 判断列是否有数据并删除行
    df_copy = df.copy()
    df_copy = df_copy[['id', '往来方', '挂账单位名', '对方单位名']].dropna(how='any', axis=0)
    df_copy = df.iloc[df_copy['id'].values]
    for i in range(len(df_copy)):
        df_copy.iloc[i]['期末余额（借正贷负）'] = float(df_copy.iloc[i]['期末余额（借正贷负）'])

    # print(df_copy)

    wl = ['往来方', '挂账单位名', '对方单位名']

    _ = {}
    for i in range(len(df_copy)):
        s = df_copy.iloc[i]
        wgd = '##'.join([s[wl[0]], s[wl[1]], s[wl[2]]])
        tmp = _.get(wgd, '#')
        if tmp == '#':
            _.setdefault(wgd, [(s['期末余额（借正贷负）'], s['id'])])
        else:
            _[wgd].append((s['期末余额（借正贷负）'], s['id']))
    _2 = {}
    for i in _.keys():
        a = [j[0] for j in _[i]]
        b = [k[1] for k in _[i]]
        for j in a:
            if isinstance(j, float) or isinstance(j, int):
                pass
            else:
                pass
                print(i, a)
        _2.setdefault(i, [sum([j[0] for j in _[i]]), [k[1] for k in _[i]]])

    _3 = {}
    for i in _2.keys():
        _2key = i.split('##')
        tmp = _3.get(_2key[0], '#')
        if tmp == '#':
            _3.setdefault(_2key[0], [_2key[1:] + _2[i]])
        else:
            _3[_2key[0]].append(_2key[1:] + _2[i])

    _4 = []
    for i in _3:
        if len(_3[i]) == 2 and _3[i][0][0] == _3[i][1][1] and _3[i][0][1] == _3[i][1][0]:
            _4.append([str(_3[i][0][-1]), _3[i][0][2]])
            _4.append([str(_3[i][1][-1]), _3[i][1][2]])
    _5 = []
    for i in range(len(_4)):
        ids = [int(j) for j in _4[i][0][1:-1].split(', ')]
        for j in ids:
            _5.append(list(df_copy[df_copy['id'] == j].values[0]) + [_4[i][-1]])

    df2 = pd.DataFrame(_5, columns=list(df_copy.columns) + ['按往来方分组的挂账单位汇总额'])

    df2.sort_values(by=['往来方', '挂账单位名', '往来科目'])

    df2.to_excel(r'C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\对比资产负债表\对比资产负债表_结果\对比关联往来合并结果.xlsx', index=False)


if __name__ == '__main__':
    hb_gljy()
    # hb_zcfzb()
    hb_glwl()
    # db_glwl()
    # os.remove(r'C:\盘锦相关企业审计项目合表工具\合并关联往来_合并关联交易_汇总资产负债表_对比关联往来\合并关联往来\合并关联往来_结果\关联往来合并结果.csv')